const express = require('express');
const router = express.Router();
const mysql = require('mysql');
const constant = require('../data/constant');
const auth = require('../middleware/auth');
const bcrypt = require('bcrypt');
const tools = require('../data/tools');
var execSync = require('child_process').execSync;


// 用户管理-获取用户信息
router.get('/get_users',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM user where role < 4;"// 不显示系统管理员
            connection.query(sql,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.length >= 1){
                        let result = [];
                        for(let i in res_data){
                            let type = '';
                            let status = null;
                            switch(res_data[i].role){
                                case 0:type = '学生';break;
                                case 1:type = '教师';break;
                                case 2:type = '企业用户';break;
                                case 3:type = '库管理员';break;
                                case 4:type = '系统管理员';break;
                            }
                            if(res_data[i].status == 1){
                                status = true;
                            }else{
                                status = false;
                            }
                            let temp = {
                                'id':res_data[i].uid,
                                'username':res_data[i].username,
                                'role':type,
                                'status':status
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"获取用户信息失败，请稍后重试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 用户管理-修改用户权限
router.post('/change_role',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "update user set role = ? where uid=?"
            let  sqlParams = [
                req.body.role,
                req.body.uid
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！请确认后重试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 获取个人资产的接口
router.post('/personal_asset',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM files WHERE uploader = ?;"
            let  sqlParams = [
                req.body.uid,
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.length >= 1){
                        let result = [];
                        for(let i in res_data){
                            let temp = {
                                'id':res_data[i].file_id,
                                'name':res_data[i].name,
                                'type':res_data[i].type,
                                'time':res_data[i].time,
                                'score':res_data[i].score
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"您没有上传任何资产！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
// 获取个人消息通知的接口
router.get('/message',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM message WHERE user = ?;"
            let  sqlParams = [
                req.query.uid,
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.length >= 1){
                        let result = [];
                        let no_read_count = 0;
                        for(let i in res_data){
                            let isRead = '';
                            if(res_data[i].isRead){
                                isRead = 'read';
                            }else{
                                isRead = 'no read';
                                no_read_count++;
                            }
                            let temp = {
                                'id':res_data[i].message_id,
                                'title':res_data[i].title,
                                'content':res_data[i].content,
                                'time':res_data[i].time,
                                'isRead':isRead
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result,
                                no_read:no_read_count
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"您没有任何新消息！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 处理已读消息的接口
router.post('/read',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "update message set isRead=? WHERE message_id = ?;"
            let  sqlParams = [
                true,
                req.body.message_id,
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:"提交成功！"
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"提交已读消息失败！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
// 用户管理-修改用户权限
router.post('/change_role',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "update user set role = ? where uid=?"
            let  sqlParams = [
                req.body.role,
                req.body.uid
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！请确认后重试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
// 用户管理-修改用户状态
router.post('/change_status',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // let status = 1;
            // if(req.body.value){
            //     status = 1;
            // }else{
            //     status = 0;
            // }
            let  sql = "update user set status = ? where uid=?"
            let  sqlParams = [
                req.body.value,
                req.body.uid
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:'用户状态更改成功！'
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！请确认后重试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 资产管理-入库审核
router.get('/warehousing',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "select * from files where status = 2";
            connection.query(sql,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.length >= 1){
                        // 处理数据
                        let result = [];
                        for(let i in res_data){
                            let temp = {
                                'id':res_data[i].file_id,
                                'name':res_data[i].name,
                                'version':res_data[i].version,
                                'type':res_data[i].type,
                                'desc':res_data[i].desc,
                                'location':'目录',
                                'tags':res_data[i].tags,
                                'path':res_data[i].path,
                                'originalname':res_data[i].originalname,
                                'uid':res_data[i].uploader
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"暂时待审核的资源！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
// 资产管理-入库审核-审核通过
router.post('/certification_pass',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let sql = '';
            let sqlParams = [];
            if(req.body.type == 'success'){
                sql = "update files set status = 3 where file_id = ?;"+
                "insert into message(title,content,user) values(?,?,?);";
                sqlParams = [
                    req.body.file_id,
                    '资产审核成功通知',
                    '恭喜您，您的上传的资产（id为'+req.body.file_id+',名称为《'+req.body.name+'》）的资产审核成功通过！',
                    req.body.uid
                ]
            }else{
                sql = "update files set status = 4 where file_id = ?;"+
                "insert into message(title,content,user) values(?,?,?);";
                sqlParams = [
                    req.body.file_id,
                    '资产审核失败通知',
                    '很抱歉，您的上传的资产（id为'+req.body.file_id+',名称为《'+req.body.name+'》）的资产审核未通过！请检查以下几点之后重新上传：（1）上传位置是否正确（2）资产格式是否选择正确（3）资产信息是否完整且如实填写。',
                    req.body.uid
                ]
            }
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data[0].affectedRows == 1 && res_data[1].affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:'提交成功！'
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！请确认后重试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 数据库备份
router.get('/db_backup',auth, (req, res) => {
    // execSync("mysqldump -uroot -p'19990713' library > /temp/backup.sql", (err, stdout, stderr) => {
    //     //将mysql执行命令作为参数输入
    //     if(err) {
    //         console.log(err);
    //         res.send({
    //             res:{
    //                 ok:0,
    //                 msg:"备份失败！"
    //             }
    //         })
    //         return;
    //     }
    //     console.log("同步备份中.....")
    //     console.log(`stdout: ${stdout}`);
    //     console.log(`stderr: ${stderr}`);
    // })
    // console.log("备份结束");
    // res.send({
    //     res:{
    //         ok:0,
    //         msg:"备份成功！备份文件已存储至'backup.sql',可通过'域名+backup.sql'进行访问下载!"
    //     }
    // })
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "backup database library to disk='temp\\db.bak'";
            connection.query(sql,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:'备份成功！'
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！请确认后重试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 申请认证
router.post('/commit_certification',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "insert into certification_application(uid,target_role,username,real_name,work,work_number) values(?,?,?,?,?,?);";
            let sqlParams = [
                req.body.uid,
                req.body.target_role,
                req.body.username,
                req.body.real_name,
                req.body.work,
                req.body.work_number
            ]
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    if(err.errno == 1062){
                        res.statusCode = 200;
                        res.send({
                            res:{
                                ok:0,
                                msg:"您已有待审核的申请，请勿重复申请！"
                            }
                        })
                    }else{
                        res.statusCode = 500;
                        res.send({
                            'res':{
                                'ok': 0,
                                'msg':'数据库连接失败'
                            }
                        });
                    }
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:'提交成功！申请结果将以站内信的形式通知您'
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！请确认后重试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
// 用户认证-获取认证信息
router.get('/certification_list',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "select * from certification_application where status = 0";
            connection.query(sql,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.length >= 1){
                        // 处理数据
                        let result = [];
                        for(let i in res_data){
                            let certification = '教师';
                            if(res_data[i].target_role != 1){
                                certification = '企业用户'
                            }
                            let temp = {
                                'id':res_data[i].uid,
                                'username':res_data[i].username,
                                'target_role':res_data[i].target_role,
                                'certification':certification,
                                'realname':res_data[i].real_name,
                                'work':res_data[i].work,
                                'work_number':res_data[i].work_number,
                                'time':tools.convert_time(res_data[i].time)
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"暂时没有待审核的申请！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 用户认证-审核
router.post('/certification',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let status = 0;
            let sql = '';
            let sqlParams = [];
            if(req.body.type == 'fail'){
                status = 2;
                role = 0;
                sql = "update certification_application set status = ? where uid = ?;"+
            "delete from certification_application where uid = ?;"+
            "insert into message(title,content,user) values(?,?,?);";
                sqlParams = [status,req.body.uid,req.body.uid,'认证审核通知','对不起，您的身份认证审核未通过，请检查信息后重新提交！',req.body.uid];
            }else{
                status = 1;
                role = req.body.target_role;
                sql = "update certification_application set status = ? where uid = ?;"+
            "update user set role = ? where uid = ?;"+
            "insert into message(title,content,user) values(?,?,?);";
                sqlParams = [
                    status,
                    req.body.uid,
                    role,
                    req.body.uid,
                    '认证审核通知',
                    '恭喜您，您的身份认证审核通过！',
                    req.body.uid
                ]
            }
            console.log(role);
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data[0].affectedRows == 1 && res_data[1].affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:'已通过该申请！'
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});
module.exports = router;